-- This SQL script is to delete a CERA entry (of entry_id &1) and the 
--    related records of the other tables (records of relations are 
--    deleted by cascade).
--
spool delEntry
alter table COVERAGE    disable constraint COVE_TEMP;
alter table COVERAGE    disable constraint COVE_SPAT;
alter table ENTRY       disable constraint ENTR_QUAL;
alter table ENTRY       disable constraint ENTR_SUMM;
alter table PARAMETER   disable constraint PARA_DACC;
alter table DATA_ACCESS disable constraint DACC_STOR_1;
alter table DATA_ACCESS disable constraint DACC_STOR_2;
alter table DATA_ACCESS disable constraint DACC_STOR_3;
alter table DATA_ACCESS disable constraint DACC_STOR_4;

delete from TEMPORAL_COVERAGE 
 where temporal_coverage_id        in (select temporal_coverage_id from COVERAGE  where entry_id =&1)
   and temporal_coverage_id    NOT in (select temporal_coverage_id from COVERAGE  where entry_id<>&1);
delete from  SPATIAL_COVERAGE
 where  spatial_coverage_id        in (select  spatial_coverage_id from COVERAGE  where entry_id =&1)
   and  spatial_coverage_id    NOT in (select  spatial_coverage_id from COVERAGE  where entry_id<>&1);
delete from QUALITY           
 where          quality_id         in (select           quality_id from ENTRY     where entry_id =&1)
   and          quality_id     NOT in (select           quality_id from ENTRY     where entry_id<>&1);
delete from SUMMARY           
 where          summary_id         in (select           summary_id from ENTRY     where entry_id =&1)
   and          summary_id     NOT in (select           summary_id from ENTRY     where entry_id<>&1);

delete from STORAGE 
 where storage_id     in (select storage1_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id =&1)
   and storage_id NOT in (select storage1_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage2_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage3_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage4_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1);

delete from STORAGE 
 where storage_id     in (select storage2_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id =&1)
   and storage_id NOT in (select storage1_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage2_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage3_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage4_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1);

delete from STORAGE 
 where storage_id     in (select storage3_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id =&1)
   and storage_id NOT in (select storage1_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage2_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage3_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage4_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1);

delete from STORAGE 
 where storage_id     in (select storage4_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id =&1)
   and storage_id NOT in (select storage1_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage2_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage3_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1
                   UNION  select storage4_id from DATA_ACCESS,PARAMETER 
         where PARAMETER.data_access_id = DATA_ACCESS.data_access_id and PARAMETER.entry_id<>&1);
             
delete from DATA_ACCESS       
 where          data_access_id     in (select       data_access_id from PARAMETER where entry_id =&1)
   and          data_access_id NOT in (select       data_access_id from PARAMETER where entry_id<>&1);

delete from ENTRY             where entry_id=&1;

alter table COVERAGE    enable constraint COVE_TEMP;
alter table COVERAGE    enable constraint COVE_SPAT;
alter table ENTRY       enable constraint ENTR_QUAL;
alter table ENTRY       enable constraint ENTR_SUMM;
alter table PARAMETER   enable constraint PARA_DACC;
alter table DATA_ACCESS enable constraint DACC_STOR_1;
alter table DATA_ACCESS enable constraint DACC_STOR_2;
alter table DATA_ACCESS enable constraint DACC_STOR_3;
alter table DATA_ACCESS enable constraint DACC_STOR_4;
spool off
