6.5.2 SQL code to remove previous revisions

Rhythmyx Code & Snippet Repository
1

We got this code from PSO. It deletes all but the current content revision.


create table CURRENTREVISIONS as
select contentid,revisionid from (
select contentid,currentrevision as revisionid from contentstatus
union
select contentid,tiprevision as revisionid from contentstatus where tiprevision >0
union
select contentid,editrevision as revisionid from contentstatus where editrevision >0
union
select contentid,revisionid as revisionid from contentstatushistory where contentstatushistoryid in (select max(contentstatushistoryid) from contentstatushistory   where valid='Y' group by contentid)) UNION_TABLE;
commit;
BEGIN
FOR R1Cursor IN (SELECT table_name FROM user_tab_cols t WHERE column_name='REVISIONID' and exists (select column_name from user_tab_cols t2 where t2.table_name=t.table_name and t2.column_name='CONTENTID') and table_name not in ('RXRELATEDCONTENT','CURRENTREVISIONS') and num_distinct is not null) LOOP
DBMS_OUTPUT.PUT_LINE( 'DELETE  from ' || R1Cursor.table_name || ' mytable where not exists (select * from CURRENTREVISIONS where contentid=mytable.contentid and revisionid=mytable.revisionid);');
EXECUTE IMMEDIATE( 'DELETE  from ' || R1Cursor.table_name || ' mytable where not exists (select * from CURRENTREVISIONS where contentid=mytable.contentid and revisionid=mytable.revisionid)');
END LOOP;
    DELETE FROM PSX_OBJECTRELATIONSHIP MYTABLE WHERE NOT EXISTS (SELECT * FROM CURRENTREVISIONS WHERE CONTENTID=MYTABLE.OWNER_ID and (REVISIONID=MYTABLE.OWNER_REVISION OR MYTABLE.OWNER_REVISION=-1));
    DELETE FROM PSX_OBJECTRELATIONSHIP MYTABLE WHERE NOT EXISTS (SELECT * FROM CURRENTREVISIONS WHERE CONTENTID=MYTABLE.DEPENDENT_ID and (REVISIONID=MYTABLE.DEPENDENT_REVISION OR MYTABLE.DEPENDENT_REVISION=-1));
    COMMIT;
END;
/
2

Please note that this code is very dangerous in the wrong hands.

For one thing, if you have ANY items in quickedit state, it is likely that you will destroy data if you run this script.

Dave