Project

General

Profile

metacat / src / system_metadata_revisions.sql @ 8427

1
/**
2
 * Correct generated System Metadata entries
3
 * 1. find system metadata with incomplete revision history
4
 */
5

    
6
-- these are old (obsoleted) entries that are not marked as such
7
select sm.guid, sm.obsoleted_by, sm.obsoletes, sm_by.guid as should_be_obsoleted_by
8
from systemmetadata sm, systemmetadata sm_by
9
where sm.guid = sm_by.obsoletes
10
and sm.obsoleted_by is null;
11
-- update them
12
BEGIN;
13
update systemmetadata sm
14
set obsoleted_by = sm_by.guid,
15
date_modified = now()
16
from systemmetadata sm_by
17
where sm.guid = sm_by.obsoletes
18
and sm.obsoleted_by is null;
19
--ROLLBACK;
20
COMMIT;
21

    
22
-- these are ones that should be marked as newer revisions
23
select sm.guid, sm.obsoleted_by, sm.obsoletes, sm_s.guid as should_obsolete
24
from systemmetadata sm, systemmetadata sm_s
25
where sm.guid = sm_s.obsoleted_by
26
and sm.obsoletes is null;
27
-- update them
28
BEGIN;
29
update systemmetadata sm
30
set obsoletes = sm_s.guid,
31
date_modified = now()
32
from systemmetadata sm_s
33
where sm.guid = sm_s.obsoleted_by
34
and sm.obsoletes is null;
35
--ROLLBACK;
36
COMMIT;
37

    
38
-- these are ones that should be marked as archived=true but are not
39
select sm. guid --count(sm.guid)
40
from systemmetadata sm, identifier id
41
where sm.guid = id.guid
42
and not exists (select * from xml_documents doc where doc.docid = id.docid and doc.rev = id.rev)
43
and sm.archived != true
44
and sm.obsoleted_by is null;
45

    
46
-- update them
47
BEGIN;
48
update systemmetadata sm
49
set archived = true,
50
date_modified = now()
51
from identifier id
52
where sm.guid = id.guid
53
and not exists (select * from xml_documents doc where doc.docid = id.docid and doc.rev = id.rev)
54
and sm.archived != true
55
and sm.obsoleted_by is null;
56
COMMIT;
57
--ROLLBACK;