def test_query_logs_final_sql(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
q.params = {'foo': 'bar'}
q.log(None)
self.assertEqual(1, QueryLog.objects.count())
log = QueryLog.objects.first()
self.assertEqual(log.sql, "select 'bar';")
def test_get_run_count(self):
q = SimpleQueryFactory()
self.assertEqual(q.get_run_count(), 0)
expected = 4
for i in range(0, expected):
q.log()
self.assertEqual(q.get_run_count(), expected)
def test_simple_query_runs(self):
expected_csv = 'two\r\n2\r\n'
r = SimpleQueryFactory()
result = r.csv_report()
self.assertIsNotNone(result, "Query '%s' returned None." % r.title)
self.assertEqual(result.lower(), expected_csv)
def test_query_log(self):
self.assertEqual(0, QueryLog.objects.count())
q = SimpleQueryFactory()
q.log(None)
self.assertEqual(1, QueryLog.objects.count())
log = QueryLog.objects.first()
self.assertEqual(log.run_by_user, None)
self.assertEqual(log.query, q)
self.assertFalse(log.is_playground)
def test_avg_duration(self):
q = SimpleQueryFactory()
self.assertIsNone(q.avg_duration())
expected = 2.5
ql = q.log()
ql.duration = 2
ql.save()
ql = q.log()
ql.duration = 3
ql.save()
self.assertEqual(q.avg_duration(), expected)
def setUp(self):
self.user = User.objects.create_superuser('admin', '*****@*****.**', 'pwd')
self.user2 = User.objects.create_superuser('admin2', '*****@*****.**', 'pwd')
self.client.login(username='******', password='******')
self.query = SimpleQueryFactory.build()
self.data = model_to_dict(self.query)
self.data["created_by_user"] = 2
def setUp(self):
self.user = User.objects.create_superuser("admin", "*****@*****.**", "pwd")
self.user2 = User.objects.create_superuser("admin2", "*****@*****.**", "pwd")
self.client.login(username="******", password="******")
self.query = SimpleQueryFactory.build()
self.data = model_to_dict(self.query)
self.data["created_by_user"] = 2
def test_avg_duration(self):
q = SimpleQueryFactory()
self.assertIsNone(q.avg_duration())
expected = 2.5
ql = q.log()
ql.duration = 2
ql.save()
ql = q.log()
ql.duration = 3
ql.save()
self.assertEqual(q.avg_duration(), expected)
def test_change_permission_required_to_save_query(self):
old = app_settings.EXPLORER_PERMISSION_CHANGE
app_settings.EXPLORER_PERMISSION_CHANGE = lambda u: False
query = SimpleQueryFactory()
expected = query.sql
resp = self.client.get(
reverse("query_detail", kwargs={'query_id': query.id}))
self.assertTemplateUsed(resp, 'explorer/query.html')
self.client.post(
reverse("query_detail", kwargs={'query_id': query.id}),
{'sql': 'select 1;'})
self.assertEqual(Query.objects.get(pk=query.id).sql, expected)
app_settings.EXPLORER_PERMISSION_CHANGE = old
def test_query_snapshot_renders(self, mocked_conn):
conn = Mock()
conn.list = Mock()
k1 = Mock()
k1.generate_url.return_value = 'http://s3.com/foo'
k1.last_modified = '2015-01-01'
k2 = Mock()
k2.generate_url.return_value = 'http://s3.com/bar'
k2.last_modified = '2015-01-02'
conn.list.return_value = [k1, k2]
mocked_conn.return_value = conn
query = SimpleQueryFactory(sql="select 1;", snapshot=True)
resp = self.client.get(
reverse("query_detail", kwargs={'query_id': query.id}))
self.assertContains(resp, '2015-01-01')
self.assertContains(resp, '2015-01-02')
def test_query_snapshot_renders(self, mocked_conn):
conn = Mock()
conn.list = Mock()
conn.list.return_value = [{
'key': 'foo-snapshot',
'last_modified': '2015-01-01'
}, {
'key': 'bar-snapshot',
'last_modified': '2015-01-02'
}]
mocked_conn.return_value = conn
query = SimpleQueryFactory(sql="select 1;", snapshot=True)
resp = self.client.get(
reverse("query_detail", kwargs={'query_id': query.id}))
self.assertContains(resp, '2015-01-01')
self.assertContains(resp, '2015-01-02')
self.assertContains(resp, settings.EXPLORER_S3_BUCKET)
def test_async_results(self, mocked_s3):
s3_client = Mock()
s3_client.generate_presigned_url = Mock()
s3_client.generate_presigned_url.return_value = 'http://s3.com/your-file.csv'
mocked_s3.return_value = s3_client
q = SimpleQueryFactory(sql='select 1 "a", 2 "b", 3 "c";',
title="testquery")
execute_query(q.id, '*****@*****.**')
output = StringIO()
output.write('a,b,c\r\n1,2,3\r\n')
self.assertEqual(len(mail.outbox), 1)
self.assertIn('[SQL Explorer] Report ', mail.outbox[0].subject)
# self.assertEqual(conn.upload.call_args[0][1].getvalue(), output.getvalue())
self.assertEqual(mocked_s3.upload_fileobj.call_count, 1)
def test_async_results(self, mocked_upload):
mocked_upload.return_value = 'http://s3.com/your-file.csv'
q = SimpleQueryFactory(sql='select 1 "a", 2 "b", 3 "c";',
title="testquery")
execute_query(q.id, '*****@*****.**')
output = StringIO()
output.write('a,b,c\r\n1,2,3\r\n')
self.assertEqual(len(mail.outbox), 2)
self.assertIn('[SQL Explorer] Your query is running',
mail.outbox[0].subject)
self.assertIn('[SQL Explorer] Report ', mail.outbox[1].subject)
self.assertEqual(mocked_upload.call_args[0][1].getvalue(),
output.getvalue())
self.assertEqual(mocked_upload.call_count, 1)
def test_async_results_failswith_message(self, mocked_s3):
conn = Mock()
conn.upload = Mock()
conn.upload.return_value = type('obj', (object, ),
{'url': 'http://s3.com/your-file.csv'})
mocked_s3.return_value = conn
q = SimpleQueryFactory(sql='select x from foo;', title="testquery")
execute_query(q.id, '*****@*****.**')
output = StringIO()
output.write('a,b,c\r\n1,2,3\r\n')
self.assertEqual(len(mail.outbox), 1)
self.assertIn('[SQL Explorer] Report ', mail.outbox[0].subject)
self.assertEqual(conn.upload.call_args[0][1].getvalue(),
"no such table: foo")
def test_failing_blacklist_means_query_doesnt_execute(self, mocked_conn):
# I should really learn to set up mocks correctly because this CANT be the most efficient way...
cursor_result = Mock()
cursor_result.fetchall.return_value = []
cursor_result.description = [('foo', 'bar')]
conn = Mock()
conn.cursor.return_value = cursor_result
mocked_conn.return_value = conn
query = SimpleQueryFactory(sql="select 1;")
resp = self.client.post(reverse("query_detail", kwargs={'query_id': query.id}), data={'sql': "select 'delete';"})
self.assertTemplateUsed(resp, 'explorer/query.html')
self.assertContains(resp, MSG_FAILED_BLACKLIST % '')
# Feels fragile, but nor sure how else to access the called-with params of .execute
self.assertEqual(conn.cursor.mock_calls[1][1][0], "select 1;")
def test_tab_delim_in_download(self):
q = SimpleQueryFactory(sql="select 1, 2;")
url = '%s?delim=tab' % reverse("query_download", kwargs={'query_id': q.id})
resp = self.client.get(url)
self.assertContains(resp, "1\t2")
def test_params_in_download(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
url = '%s?params=%s' % (reverse("query_download", kwargs={'query_id': q.id}), 'foo:123')
resp = self.client.get(url)
self.assertContains(resp, "'123'")
def test_query_with_bad_sql_renders_error_on_save(self):
query = SimpleQueryFactory(sql="select 1;")
resp = self.client.post(reverse("query_detail", kwargs={'query_id': query.id}), data={'sql': 'error'})
self.assertTemplateUsed(resp, 'explorer/query.html')
self.assertContains(resp, "syntax error")
def test_run_count(self):
q = SimpleQueryFactory(title='foo - bar1')
for i in range(0, 4):
q.log()
resp = self.client.get(reverse("explorer_index"))
self.assertContains(resp, '<td>4</td>')
def test_query_saves_to_log(self):
query = SimpleQueryFactory()
data = model_to_dict(query)
data['sql'] = 'select 12345;'
self.client.post(reverse("query_detail", kwargs={'query_id': query.id}), data)
self.assertEqual(1, QueryLog.objects.count())
def test_saving_non_executing_query_with__wrong_url_params_works(self):
q = SimpleQueryFactory(sql="select $$swap$$;")
data = model_to_dict(q)
url = '%s?params=%s' % (reverse("query_detail", kwargs={'query_id': q.id}), 'foo:123')
resp = self.client.post(url, data)
self.assertContains(resp, 'saved')
def test_stream_csv_from_query(self):
q = SimpleQueryFactory()
resp = self.client.get(reverse("query_csv", kwargs={'query_id': q.id}))
self.assertEqual('text', resp['content-type'])
def test_playground_renders_with_query_sql(self):
query = SimpleQueryFactory(sql="select 1;")
resp = self.client.get('%s?query_id=%s' % (reverse("explorer_playground"), query.id))
self.assertTemplateUsed(resp, 'explorer/play.html')
self.assertContains(resp, 'select 1;')
def test_form_is_always_valid_with_params(self):
q = SimpleQueryFactory(sql="select $$a$$;")
q.params = {}
form = QueryForm(model_to_dict(q))
self.assertTrue(form.is_valid())
def test_params_get_merged(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
q.params = {"foo": "bar", "mux": "qux"}
self.assertEqual(q.available_params(), {"foo": "bar"})
def test_form_fails_blacklist_even_with_params(self):
q = SimpleQueryFactory(sql="delete $$a$$;", created_by_user_id=None)
q.params = {}
form = QueryForm(model_to_dict(q))
self.assertFalse(form.is_valid())
def test_default_params_used(self):
q = SimpleQueryFactory(sql="select '$$foo:bar$$';")
self.assertEqual(q.available_params(), {'foo': 'bar'})
def test_log_saves_duration(self):
q = SimpleQueryFactory()
res, ql = q.execute_with_logging(None)
log = QueryLog.objects.first()
self.assertEqual(log.duration, res.duration)
def test_query_with_no_resultset_doesnt_throw_error(self):
query = SimpleQueryFactory(sql="")
resp = self.client.get('%s?query_id=%s' % (reverse("explorer_playground"), query.id))
self.assertTemplateUsed(resp, 'explorer/play.html')
def test_blacklist_prevents_bad_sql_from_executing(self):
q = SimpleQueryFactory(sql='select 1 "delete";')
headers, data, duration, error = q.headers_and_data()
self.assertEqual(error, MSG_FAILED_BLACKLIST)
def setUp(self):
self.user = User.objects.create_superuser('admin', '*****@*****.**', 'pwd')
self.client.login(username='******', password='******')
self.query = SimpleQueryFactory(sql="select $$swap$$;")
def test_blacklist_check_runs_before_execution(self):
q = SimpleQueryFactory(sql='select 1;')
with AssertMethodIsCalled(q, "passes_blacklist"):
headers, data, duration, error = q.headers_and_data()
def test_creating_query_does_not_save_to_log(self):
query = SimpleQueryFactory()
self.client.post(reverse("query_create"), model_to_dict(query))
self.assertEqual(0, QueryLog.objects.count())
def test_params_get_merged(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
q.params = {'foo': 'bar', 'mux': 'qux'}
self.assertEqual(q.available_params(), {'foo': 'bar'})
def test_is_playground(self):
self.assertTrue(QueryLog(sql='foo').is_playground)
q = SimpleQueryFactory()
self.assertFalse(QueryLog(sql='foo', query_id=q.id).is_playground)
def test_blacklist_prevents_bad_sql_with_params_from_executing(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
q.params = {"foo": "'; delete from *; select'"}
res = q.headers_and_data()
self.assertEqual(res.error, MSG_FAILED_BLACKLIST)
def test_query_with_bad_sql_renders_error(self):
query = SimpleQueryFactory(sql="error")
resp = self.client.get(reverse("query_detail", kwargs={'query_id': query.id}))
self.assertTemplateUsed(resp, 'explorer/query.html')
self.assertContains(resp, "syntax error")
def test_admin_required(self):
self.client.logout()
query = SimpleQueryFactory()
resp = self.client.get(reverse("query_detail", kwargs={'query_id': query.id}))
self.assertTemplateUsed(resp, 'admin/login.html')
def test_get_params_for_request_empty(self):
q = SimpleQueryFactory()
self.assertEqual(get_params_for_url(q), None)
def test_final_sql_uses_merged_params(self):
q = SimpleQueryFactory(sql="select '$$foo:bar$$', '$$qux$$';")
q.params = {'qux': 'mux'}
expected = "select 'bar', 'mux';"
self.assertEqual(q.final_sql(), expected)
def test_params_get_merged(self):
q = SimpleQueryFactory(sql="select '$$foo$$';")
q.params = {'foo': 'bar', 'mux': 'qux'}
self.assertEqual(q.available_params(), {'foo': 'bar'})
def test_fullscreen(self):
query = SimpleQueryFactory(sql="select 1;")
resp = self.client.get(
reverse("query_detail", kwargs={'query_id': query.id}) +
'?fullscreen=1')
self.assertTemplateUsed(resp, 'explorer/fullscreen.html')
def test_blacklist_prevents_bad_sql_from_executing(self):
q = SimpleQueryFactory(sql='select 1 "delete";')
headers, data, duration, error = q.headers_and_data()
self.assertEqual(error, MSG_FAILED_BLACKLIST)
def test_fullscreen(self):
query = SimpleQueryFactory(sql="")
resp = self.client.get('%s?query_id=%s&fullscreen=1' %
(reverse("explorer_playground"), query.id))
self.assertTemplateUsed(resp, 'explorer/fullscreen.html')
def test_blacklist_check_runs_before_execution(self):
q = SimpleQueryFactory(sql='select 1;')
with AssertMethodIsCalled(q, "passes_blacklist"):
headers, data, duration, error = q.headers_and_data()
def test_modified_date_gets_updated_after_viewing_query(self):
query = SimpleQueryFactory()
old = query.last_run_date
time.sleep(0.1)
self.client.get(reverse("query_detail", kwargs={'query_id': query.id}))
self.assertNotEqual(old, Query.objects.get(pk=query.id).last_run_date)
def test_run_count(self):
q = SimpleQueryFactory(title='foo - bar1')
for i in range(0, 4):
q.log()
resp = self.client.get(reverse("explorer_index"))
self.assertContains(resp, '<td>4</td>')
def test_doesnt_render_results_if_show_is_none(self):
query = SimpleQueryFactory(sql='select 6870+1;')
resp = self.client.get(reverse("query_detail", kwargs={'query_id': query.id}) + '?show=0')
self.assertTemplateUsed(resp, 'explorer/query.html')
self.assertNotContains(resp, '6871')