SET serveroutput ON
declare 
-- CUSTOMIZE START
path OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_PATH%type := '/sites/noeastro/de/_test/videotest.jsp';
simulate VARCHAR(16) := 'true';
-- CUSTOMIZE END
rid OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID%type;
sid OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_ID%type;
childcount number;
siblingcount number;
 
TYPE ProjectList IS VARRAY(2) OF VARCHAR(16);
projects ProjectList := ProjectList('ONLINE','OFFLINE');
project VARCHAR(16);
begin
FOR i IN projects.FIRST .. projects.LAST
LOOP
project := projects(i);
DBMS_OUTPUT.NEW_LINE;
dbms_output.put_line('Project: '||project);
-- get the resource id of the resource to delete:
SELECT RESOURCE_ID INTO rid FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE RESOURCE_PATH LIKE path;
SELECT STRUCTURE_ID INTO sid FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE RESOURCE_PATH LIKE path;
dbms_output.put_line('Resource ID to delete: '||rid);
dbms_output.put_line('Structure ID to delete: '||sid);
-- check if we deal with a folder: 
SELECT count(*) INTO childcount FROM OPENCMS.CMS_OFFLINE_STRUCTURE WHERE PARENT_ID = sid;
IF childcount > 0 then 
  dbms_output.put_line(path||' has '|| childcount || ' direct children. Delete those first.');
else 
  dbms_output.put_line('No children found, ok to continue');
  -- check siblings 
  EXECUTE IMMEDIATE 'SELECT count(*) from OPENCMS.CMS_'||project||'_STRUCTURE WHERE RESOURCE_ID ='''||rid||''''  INTO siblingcount;
  dbms_output.put_line('Detected '||siblingcount||' siblings.');
 
  -- delete contents: special there is no table CMS_ONLINE_CONTENTS
  IF project = 'OFFLINE' then                                                                
    EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_OFFLINE_CONTENTS WHERE RESOURCE_ID = '''||rid||'''';
  else 
    EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_CONTENTS WHERE RESOURCE_ID = '''||rid||'''';
  end IF;
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_'||project||'_CONTENTS.');
  -- delete access control
  EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_ACCESSCONTROL WHERE RESOURCE_ID ='''||rid||'''';
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_'||project||'_ACCESSCONTROL.');
  -- delete individual properties: 
  EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=1 AND PROPERTY_MAPPING_ID = '''||sid||'''';
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' individual properties from CMS_'||project||'_PROPERTIES.');
  -- delete shared properties (if no siblings left)
  IF siblingcount = 1 then
    EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=2 AND PROPERTY_MAPPING_ID = '''||rid||'''';
    dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' shared properties from CMS_'||project||'_PROPERTIES as no siblings are left.');
  else 
    dbms_output.put_line('Deleted no shared properties from CMS_'||project||'_PROPERTIES as '||siblingcount||' siblings are left.');
  end IF;
  -- delete resource relations
  EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_RESOURCE_RELATIONS WHERE RELATION_SOURCE_ID='''||sid||'''';
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' relation sources from CMS_'||project||'_RESOURCE_RELATIONS.');
  EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_RESOURCE_RELATIONS WHERE RELATION_TARGET_ID='''||sid||'''';
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' relation targets from CMS_'||project||'_RESOURCE_RELATIONS.');
  -- Finally delete structure entry (the thing that makes it show in the explorer)
  EXECUTE IMMEDIATE 'DELETE FROM OPENCMS.CMS_'||project||'_STRUCTURE WHERE STRUCTURE_ID='''||sid||'''';
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' structure entry from CMS_'||project||'_STRUCTURE.');
end IF;
END LOOP;
DBMS_OUTPUT.NEW_LINE;
dbms_output.put_line('Clearing History');
-- Tables that are not offline/online:
  -- delete history structure
  DELETE FROM OPENCMS.CMS_HISTORY_STRUCTURE WHERE STRUCTURE_ID=sid;
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_HISTORY_STRUCTURE.');
  -- delete history resource
  DELETE FROM OPENCMS.CMS_HISTORY_RESOURCES WHERE RESOURCE_ID=rid;
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' entries from CMS_HISTORY_RESOURCE.');
  -- delete history properties individual
  DELETE FROM OPENCMS.CMS_HISTORY_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=1 AND PROPERTY_MAPPING_ID = sid;
  dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' individual properties from CMS_HISTORY_PROPERTIES.');
  -- delete history properties shared
  IF siblingcount = 1 then
    DELETE FROM OPENCMS.CMS_HISTORY_PROPERTIES WHERE PROPERTY_MAPPING_TYPE=2 AND PROPERTY_MAPPING_ID = rid;
    dbms_output.put_line('Deleted '||SQL%ROWCOUNT||' shared properties from CMS_HISTORY_PROPERTIES as no siblings are left.');
  else 
    dbms_output.put_line('Deleted no shared properties from CMS_HISTORY_PROPERTIES as '||siblingcount||' siblings are left.');
  end IF;
  IF simulate='true' then
    rollback;
    dbms_output.put_line('Simulation: rollback');   
  else 
    commit;
    dbms_output.put_line('No Simulation: commit');   
    dbms_output.put_line('Clear the CORE CACHE (below Cache Administration) in OPENCMS to see the effect.');   
  end IF;
exception 
  when NO_DATA_FOUND then 
  dbms_output.put_line('Cannot find the resource to delete: '|| path);   
end;