/** * Correct generated System Metadata entries * 1. find system metadata with incomplete revision history */ -- these are old (obsoleted) entries that are not marked as such select sm.guid, sm.obsoleted_by, sm.obsoletes, sm_by.guid as should_be_obsoleted_by from systemmetadata sm, systemmetadata sm_by where sm.guid = sm_by.obsoletes and sm.obsoleted_by is null; -- update them BEGIN; update systemmetadata sm set obsoleted_by = sm_by.guid, date_modified = now() from systemmetadata sm_by where sm.guid = sm_by.obsoletes and sm.obsoleted_by is null; --ROLLBACK; COMMIT; -- these are ones that should be marked as newer revisions select sm.guid, sm.obsoleted_by, sm.obsoletes, sm_s.guid as should_obsolete from systemmetadata sm, systemmetadata sm_s where sm.guid = sm_s.obsoleted_by and sm.obsoletes is null; -- update them BEGIN; update systemmetadata sm set obsoletes = sm_s.guid, date_modified = now() from systemmetadata sm_s where sm.guid = sm_s.obsoleted_by and sm.obsoletes is null; --ROLLBACK; COMMIT; -- these are ones that should be marked as archived=true but are not select sm. guid --count(sm.guid) from systemmetadata sm, identifier id where sm.guid = id.guid and not exists (select * from xml_documents doc where doc.docid = id.docid and doc.rev = id.rev) and sm.archived != true and sm.obsoleted_by is null; -- update them BEGIN; update systemmetadata sm set archived = true, date_modified = now() from identifier id where sm.guid = id.guid and not exists (select * from xml_documents doc where doc.docid = id.docid and doc.rev = id.rev) and sm.archived != true and sm.obsoleted_by is null; COMMIT; --ROLLBACK;