Metacat

/** use plain old revision history **/

-- update obsoletedby

update doi_registration doi

set obsoletedBy = newer.guid

from identifier id, identifier newer

where doi.guid = id.guid

and id.docid = newer.docid

and newer.rev = (select min(next.rev) from identifier next where next.docid = id.docid and next.rev > id.rev);

-- update the obsolets

update doi_registration doi

set obsoletes = older.guid

from identifier id, identifier older

where doi.guid = id.guid

and id.docid = older.docid

and older.rev = (select max(prev.rev) from identifier prev where prev.docid = id.docid and prev.rev < id.rev);

/**

select doi.guid, older.guid as obsoletes 

from doi_registration doi, identifier id, identifier older

where doi.guid = id.guid

and id.docid = older.docid

and older.rev = (select max(prev.rev) from identifier prev where prev.docid = id.docid and prev.rev < id.rev)

and doi.guid like 'doi:10.5063/AA/ABS.4%';

**/

-- update resourceMap pointer

and xpi.nodedata like 'ecogrid%'

and id.guid in (select guid from doi_registration);

-- include revisions for described data

INSERT INTO ecogrid_docids

select distinct id.docid, id.rev, id.guid, substring(child.nodedata from 'ecogrid://knb/(.*)$') as data_docid

from identifier id, xml_revisions docs, xml_nodes_revisions nodes, xml_nodes_revisions parent, xml_nodes_revisions child

where id.docid = docs.docid

and id.rev = docs.rev

and docs.rootnodeid = nodes.rootnodeid

and nodes.nodeid = child.parentnodeid

and nodes.parentnodeid = parent.nodeid

and nodes.nodename = 'url'

and parent.nodename = 'online'

and child.nodedata like 'ecogrid%'

and child.nodetype = 'TEXT'

and id.guid in (select guid from doi_registration);

select count(*)

from doi_registration

where resourceMapId is null;