def test_preview_valid_datacut(self, client, test_db):
dataset = factories.DataSetFactory(
type=DataSet.TYPE_DATA_CUT,
user_access_type='REQUIRES_AUTHENTICATION',
)
# Check if sample data shown correctly
query1 = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query='SELECT 1 as a, 2 as b',
)
response = client.get(
reverse(
'datasets:dataset_query_preview',
kwargs={
'dataset_uuid': dataset.id,
'query_id': query1.id
},
))
response_content = response.content.decode(response.charset)
html = ''.join(
[s.strip() for s in response_content.splitlines() if s.strip()])
assert response.status_code == 200
assert '<li>a</li><li>b</li>' in html # check fields
assert ('<thead>'
'<tr class="govuk-table__row">'
'<th class="govuk-table__header ref-data-col-">a</th>'
'<th class="govuk-table__header ref-data-col-">b</th>'
'</tr>'
'</thead>'
'<tbody>'
'<tr class="govuk-table__row">'
'<td class="govuk-table__cell">1</td>'
'<td class="govuk-table__cell">2</td>'
'</tr>'
'</tbody>') in html # check sample data
assert 'Showing all rows from data.' in html
assert 'Download' in html # check download button available
# Check if sample limited to 20 random rows if more data available
preview_rows = settings.DATASET_PREVIEW_NUM_OF_ROWS
query2 = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query=f'SELECT * FROM generate_series(1, {preview_rows * 2}) as a;',
)
response = client.get(
reverse(
'datasets:dataset_query_preview',
kwargs={
'dataset_uuid': dataset.id,
'query_id': query2.id
},
))
response_content = response.content.decode(response.charset)
assert (
f'Showing <strong>{preview_rows}</strong> random rows from data.'
in response_content)
def test_preview_valid_datacut(self, access_type, client, test_db):
dataset = factories.DataSetFactory(
type=DataSetType.DATACUT,
user_access_type=access_type,
)
# Check if sample data shown correctly
query1 = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query="SELECT 1 as a, 2 as b",
)
response = client.get(
reverse(
"datasets:dataset_query_preview",
kwargs={
"dataset_uuid": dataset.id,
"query_id": query1.id
},
))
response_content = response.content.decode(response.charset)
html = "".join(
[s.strip() for s in response_content.splitlines() if s.strip()])
assert response.status_code == 200
assert "<li>a</li><li>b</li>" in html # check fields
assert ("<thead>"
'<tr class="govuk-table__row">'
'<th class="govuk-table__header ref-data-col-">a</th>'
'<th class="govuk-table__header ref-data-col-">b</th>'
"</tr>"
"</thead>"
"<tbody>"
'<tr class="govuk-table__row">'
'<td class="govuk-table__cell">1</td>'
'<td class="govuk-table__cell">2</td>'
"</tr>"
"</tbody>") in html # check sample data
assert "Showing all rows from data." in html
assert "Download" in html # check download button available
# Check if sample limited to 20 random rows if more data available
preview_rows = settings.DATASET_PREVIEW_NUM_OF_ROWS
query2 = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query=f"SELECT * FROM generate_series(1, {preview_rows * 2}) as a;",
)
response = client.get(
reverse(
"datasets:dataset_query_preview",
kwargs={
"dataset_uuid": dataset.id,
"query_id": query2.id
},
))
response_content = response.content.decode(response.charset)
assert (
f"Showing <strong>{preview_rows}</strong> random rows from data."
in response_content)
def test_query_data_cut_preview(client, dataset_db):
ds = factories.DataSetFactory.create(
user_access_type='REQUIRES_AUTHENTICATION',
published=True,
)
cut1 = factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
reviewed=True,
)
cut2 = factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
reviewed=False,
)
response = client.get(ds.get_absolute_url())
assert response.status_code == 200
# reviewed query should have a preview link
assert (f'href="/datasets/{ds.id}/query/{cut1.id}/preview"'
in response.rendered_content)
assert 'Preview' in response.rendered_content
# non reviewed query shouldn't have a preview link
assert (f'href="/datasets/{ds.id}/query/{cut2.id}/preview"'
not in response.rendered_content)
assert 'No preview available' in response.rendered_content
def test_custom_query_data_last_updated(metadata_db):
dataset = factories.DataSetFactory()
# Ensure the earliest "last updated" date is returned when
# there are multiple tables in the query
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=metadata_db,
query="select * from table1 join table2 on 1=1",
)
factories.CustomDatasetQueryTableFactory(query=query,
schema="public",
table="table1")
factories.CustomDatasetQueryTableFactory(query=query,
schema="public",
table="table2")
assert query.get_data_last_updated_date() == datetime(2020,
9,
1,
0,
1,
0,
tzinfo=UTC)
# Ensure a single table returns the last update date
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=metadata_db,
query="select * from table1",
)
factories.CustomDatasetQueryTableFactory(query=query,
schema="public",
table="table1")
assert query.get_data_last_updated_date() == datetime(2020,
9,
2,
0,
1,
0,
tzinfo=UTC)
# Ensure None is returned if we don't have any metadata for the tables
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=metadata_db,
query="select * from table3",
)
assert query.get_data_last_updated_date() is None
# Ensure None is returned if the last updated date is null
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=metadata_db,
query="select * from table4",
)
assert query.get_data_last_updated_date() is None
def _create_query(self,
sql,
reviewed=True,
published=True,
data_grid_enabled=False):
with psycopg2.connect(
self._get_dsn()) as conn, conn.cursor() as cursor:
cursor.execute("""
CREATE TABLE IF NOT EXISTS custom_query_test (
id INT,
name VARCHAR(255),
date DATE
);
TRUNCATE TABLE custom_query_test;
INSERT INTO custom_query_test VALUES(1, 'the first record', NULL);
INSERT INTO custom_query_test VALUES(2, 'the second record', '2019-01-01');
INSERT INTO custom_query_test VALUES(3, 'the last record', NULL);
""")
dataset = factories.DataSetFactory(
user_access_type=UserAccessType.REQUIRES_AUTHENTICATION,
published=published)
return factories.CustomDatasetQueryFactory(
dataset=dataset,
database=self._get_database(),
query=sql,
reviewed=reviewed,
data_grid_enabled=data_grid_enabled,
)
def test_authorised_query(self, access_type, client, test_db):
dataset = factories.DataSetFactory(type=DataSetType.MASTER,
user_access_type=access_type)
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query="SELECT 1 as a, 2 as b",
reviewed=True,
)
response = client.get(
reverse("datasets:data_cut_query_preview",
args=(dataset.id, query.id)))
assert response.status_code == 200
content = response.content.decode("utf-8")
assert ("<thead>"
'<tr class="govuk-table__row">'
'<th class="govuk-table__header">a</th>'
'<th class="govuk-table__header">b</th>'
"</tr>"
"</thead><tbody>"
'<tr class="govuk-table__row">'
'<td class="govuk-table__cell">1</td>'
'<td class="govuk-table__cell">2</td>'
"</tr></tbody>") in "".join(
[s.strip() for s in content.splitlines() if s.strip()])
assert "Showing <strong>1</strong> record." in content
assert "Download as CSV" in content
def test_query_data_cut_fields(client, dataset_db):
ds = factories.DataSetFactory.create(published=True)
factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
)
response = client.get(ds.get_absolute_url())
assert response.status_code == 200
assert response.context["fields"] == ['customid', 'customname']
def test_forbidden_dataset(self, client):
dataset = factories.DataSetFactory(user_access_type='REQUIRES_AUTHORIZATION')
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=self._get_database(),
query='SELECT * FROM a_table',
)
log_count = EventLog.objects.count()
download_count = dataset.number_of_downloads
response = client.get(query.get_absolute_url())
assert response.status_code == 403
assert EventLog.objects.count() == log_count
assert DataSet.objects.get(pk=dataset.id).number_of_downloads == download_count
def test_query_data_cut_preview_staff_user_no_access(staff_client, dataset_db):
ds = factories.DataSetFactory.create(
user_access_type=UserAccessType.REQUIRES_AUTHORIZATION,
published=True,
)
cut = factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
reviewed=False,
)
response = staff_client.get(ds.get_absolute_url())
# staff user with no access should not have a preview link
assert f'href="/datasets/{ds.id}/query/{cut.id}/preview"' not in response.rendered_content
def test_forbidden_dataset(self):
dataset = factories.DataSetFactory(
user_access_type='REQUIRES_AUTHORIZATION')
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=self.database,
query='SELECT * FROM a_table')
log_count = EventLog.objects.count()
download_count = dataset.number_of_downloads
response = self._authenticated_get(query.get_absolute_url())
self.assertEqual(response.status_code, 403)
self.assertEqual(EventLog.objects.count(), log_count)
self.assertEqual(
DataSet.objects.get(pk=dataset.id).number_of_downloads,
download_count)
def test_unauthorised_query(self, client, test_db):
dataset = factories.DataSetFactory(
type=DataSetType.MASTER,
user_access_type=UserAccessType.REQUIRES_AUTHORIZATION,
)
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query="SELECT 1 as a, 2 as b",
reviewed=False,
)
response = client.get(
reverse("datasets:data_cut_query_preview",
args=(dataset.id, query.id)))
assert response.status_code == 403
def test_preview_forbidden_datacut(self, client, dataset_db):
dataset = factories.DataSetFactory(
user_access_type='REQUIRES_AUTHORIZATION')
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=dataset_db,
query='SELECT * FROM a_table',
)
response = client.get(
reverse(
'datasets:dataset_query_preview',
kwargs={
'dataset_uuid': dataset.id,
'query_id': query.id
},
))
assert response.status_code == 403
def test_query_data_cut_preview_staff_user(staff_client, dataset_db):
ds = factories.DataSetFactory.create(
user_access_type='REQUIRES_AUTHENTICATION',
published=True,
)
cut = factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
reviewed=False,
)
response = staff_client.get(ds.get_absolute_url())
# non reviewed query should have a preview link
assert (f'href="/datasets/{ds.id}/query/{cut.id}/preview"'
in response.rendered_content)
assert 'Preview' in response.rendered_content
def _preview_unreviewed_datacut(self, client, dataset_db):
dataset = factories.DataSetFactory(
user_access_type='REQUIRES_AUTHENTICATION', published=True)
sql = 'SELECT 1 as a, 2 as b'
query = factories.CustomDatasetQueryFactory(dataset=dataset,
database=dataset_db,
query=sql,
reviewed=False)
return client.get(
reverse(
'datasets:dataset_query_preview',
kwargs={
'dataset_uuid': dataset.id,
'query_id': query.id
},
))
def test_query_data_cut_fields(client, dataset_db):
ds = factories.DataSetFactory.create(published=True)
factories.CustomDatasetQueryFactory(
dataset=ds,
database=dataset_db,
query="SELECT id customid, name customname FROM dataset_test",
)
response = client.get(
reverse(
'catalogue:dataset_fullpath',
kwargs={
'group_slug': ds.grouping.slug,
'set_slug': ds.slug
},
))
assert response.status_code == 200
assert response.context["fields"] == ['customid', 'customname']
def test_preview_forbidden_datacut(self, client, test_db):
dataset = factories.DataSetFactory(
type=DataSetType.DATACUT,
user_access_type=UserAccessType.REQUIRES_AUTHORIZATION,
)
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query="SELECT * FROM a_table",
)
response = client.get(
reverse(
"datasets:dataset_query_preview",
kwargs={
"dataset_uuid": dataset.id,
"query_id": query.id
},
))
assert response.status_code == 403
def _preview_unreviewed_datacut(self, client, test_db):
dataset = factories.DataSetFactory(
type=DataSetType.DATACUT,
user_access_type=UserAccessType.REQUIRES_AUTHENTICATION,
published=True,
)
sql = "SELECT 1 as a, 2 as b"
query = factories.CustomDatasetQueryFactory(dataset=dataset,
database=test_db,
query=sql,
reviewed=False)
return client.get(
reverse(
"datasets:dataset_query_preview",
kwargs={
"dataset_uuid": dataset.id,
"query_id": query.id
},
))
def test_preview_invalid_datacut(self, client, dataset_db):
dataset = factories.DataSetFactory(
user_access_type='REQUIRES_AUTHENTICATION')
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=dataset_db,
query='SELECT * FROM invalid_table',
)
response = client.get(
reverse(
'datasets:dataset_query_preview',
kwargs={
'dataset_uuid': dataset.id,
'query_id': query.id
},
))
response_content = response.content.decode(response.charset)
assert 'Data Fields' not in response_content
assert 'No data available' in response_content
assert 'Download' not in response_content
def test_clone_dataset_copies_related_objects(db):
ds = factories.DataSetFactory.create(published=True)
factories.DataSetUserPermissionFactory(dataset=ds)
factories.SourceLinkFactory(dataset=ds)
factories.SourceViewFactory(dataset=ds)
factories.SourceTableFactory(dataset=ds)
factories.CustomDatasetQueryFactory(dataset=ds)
clone = ds.clone()
assert not clone.datasetuserpermission_set.all()
assert [obj.dataset for obj in clone.sourcelink_set.all()] == [clone]
assert [obj.dataset for obj in clone.sourceview_set.all()] == [clone]
assert [obj.dataset for obj in clone.sourcetable_set.all()] == [clone]
assert [obj.dataset for obj in clone.customdatasetquery_set.all()] == [clone]
assert ds.datasetuserpermission_set.all()
assert [obj.dataset for obj in ds.sourcelink_set.all()] == [ds]
assert [obj.dataset for obj in ds.sourceview_set.all()] == [ds]
assert [obj.dataset for obj in ds.sourcetable_set.all()] == [ds]
assert [obj.dataset for obj in ds.customdatasetquery_set.all()] == [ds]
def test_preview_invalid_datacut(self, access_type, client, test_db):
dataset = factories.DataSetFactory(
type=DataSetType.DATACUT,
user_access_type=access_type,
)
query = factories.CustomDatasetQueryFactory(
dataset=dataset,
database=test_db,
query="SELECT * FROM invalid_table",
)
response = client.get(
reverse(
"datasets:dataset_query_preview",
kwargs={
"dataset_uuid": dataset.id,
"query_id": query.id
},
))
response_content = response.content.decode(response.charset)
assert "Data Fields" not in response_content
assert "No data available" in response_content
assert "Download" not in response_content
def _create_query(self, sql):
dataset = factories.DataSetFactory(
user_access_type='REQUIRES_AUTHENTICATION')
return factories.CustomDatasetQueryFactory(dataset=dataset,
database=self.database,
query=sql)
