Deleting broken resources via SQL

Under some circumstances it may happen that resources are corrupted in the OpenCms VFS. This e.g. happen if the database encounters an error in the middle of an "OpenCms" - atomic operation. Symptoms for this may be:

• The context menu is not available.
• A deleted folder may not be published as it contains files. Restoring these files fails e.g. because "they are not locked by you".
• The file may not be edited (the CMS_CONTENTS entry is missing).
• The resource entry of the file is missing but the structure entry is there (thus the file is shown in the explorer.
• The file exists Online but cannot be found Offline.

On this page you may find/post SQL scripts to delete such corrupt files.

There is a high risk of destroying your whole OpenCMS VFS (database) when using direct SQL to modify it. This is not recommended.
These scripts are distributed in the hope that they will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of 
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

Never use these scripts untested. Always test them on a non-productive server. If they work as desired you might want to use them on a production system. For 
this it is recommended to have a rollback plan: 
* Do it at a time when a downtime of your server does hurt the fewest.
* Dump your OpenCms database.
* Lock out users.  

 
A safer option to deal with a corrupt VFS is to set up a fresh instance, export all content from the old system via OpenCms Database Management and import this on the new system. 

OpenCms 7.0.5

Oracle 9i

This script tries to delete any trace of a bad resource by it's resource id read from the table CMS_OFFLINE_STRUCTURE. If the bad resource resides in the online project you might tweak the script. This script will not delete folders with children. You have to enter the path to the resource. Execute it with sqlplus (SQL>@file) or Toad for Oracle. By default it only simulates (rollback). In case of questions you could try to contact achim<dot>westermann<at>gmx<dot>de

SET serveroutput ON
declare 
-- CUSTOMIZE START
path OPENCMS.CMS_OFFLINE_STRUCTURE.RESOURCE_PATH%type := '/sites/default/testfile.html';
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;