How to delete Tabular Data (in DB) - BEXIS2/Core GitHub Wiki

Delete Tabular Data

  1. Important: Do not change or delete directly in the database without a current backup!
  2. 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)