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)