Metacat

/**

 * 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;