------------------------------------------------------------------------------------\012-- Extend events table\012------------------------------------------------------------------------------------\012\012ALTER TABLE events\012\011\011ADD COLUMN entity_type VARCHAR(80);\012ALTER TABLE events\012\011\011ADD COLUMN identifier VARCHAR(250);\012\011\011\012-- Creating check constraints\012\012ALTER TABLE events ADD CONSTRAINT evnt_et_enum_ck CHECK \012\011\011(entity_type IN ('ATTACHMENT', 'DATASET', 'EXPERIMENT', 'MATERIAL', 'PROJECT', 'PROPERTY_TYPE', 'SAMPLE', 'VOCABULARY')); \012\012-- simple migration of events (all old rows contain data set deletion information)\012\012UPDATE events SET \012\011\011entity_type = 'DATASET',\012\011\011identifier = data_id,\012\011\011description = data_id;\012\012ALTER TABLE events\012    DROP COLUMN data_id;\012\012-- remove deleted data sets with their properties and relations to other data sets\012\012CREATE VIEW deleted_data_ids AS\012    SELECT id FROM data WHERE is_deleted = 'TRUE';\012DELETE FROM data_set_relationships \012\011\011WHERE data_id_parent IN (SELECT * FROM deleted_data_ids) \012\011\011OR data_id_child IN (SELECT * FROM deleted_data_ids);\012DELETE FROM data_set_properties \012    WHERE ds_id IN (SELECT * FROM deleted_data_ids);\012DELETE FROM external_data\012\011\011WHERE data_id IN (SELECT * FROM deleted_data_ids);\012DELETE FROM data WHERE is_deleted = 'TRUE';    \012DROP VIEW deleted_data_ids;\012\012-- remove old columns\012\012ALTER TABLE data\012\011\011DROP COLUMN is_deleted;\012\011\011\012-- add constraints\012\012ALTER TABLE events\012    ALTER COLUMN entity_type SET NOT NULL;\012ALTER TABLE events\012    ALTER COLUMN identifier SET NOT NULL;\012ALTER TABLE events\012\011\011ADD CONSTRAINT evnt_bk_uk UNIQUE(EVENT_TYPE,ENTITY_TYPE,IDENTIFIER);\012