How to delete Tabular Data (in DB)

Jump to bottom

Franziska Zander edited this page Feb 9, 2022 · 5 revisions

Delete Tabular Data

Important: Do not change or delete directly in the database without a current backup!
Important: Only works for all uploads or the first and only upload

0. Move to trash

ensure a trash dataset id exists (e.g. create a dataset with ID 1, without any dataset versions)
ensure to empty the trash regularly (1b)

Table datasetversions

Filter by datasetref (dataset ID)
Search for dataset versions related to data upload (column m_comment: Data)
Change datasetref to trash id
Set the status of the new latest version from 0 to 2

Materialized Views

Search for associated View: Drop / Delete

1a. Delete data first and only

Table datasetversions

Select datasetversion id related to data upload

Table datatuples

Select affected entries based on datasetversion ID SELECT * FROM public.datatuples WHERE datasetversionref = '1462'
Delete affected entries

Materialized view

Delete related materialized view

1b. Delete data all

Table datasetversions

Select all datasetversion ids related to all data upload/update
Delete affected entries

Table datatupleverions

Select affected entries based on datasetversion ID SELECT * FROM public.datatuples WHERE datasetversionref = '1462'
Check if results is as expected
Delete affected entries
Set the status of the new latest version from 0 to 2

Table datatuples

Select affected entries based on datasetversion ID and check if it fits SELECT * FROM public.datatuples WHERE datasetversionref = '1462'
Check if results is as expected
Delete affected entries

Materialized view

Delete related materialized view

2. Delete dataset version(s)

Decide if the version info should / can be kept or should be deleted
- Keep: Be aware, that going back in history will show an empty result for the version related to the upload
- Remove: follow instruction delete metadata version (https://github.com/BEXIS2/Core/wiki/How-to-delete-a-Metadata-version-(in-DB))