Metacat

select 

text('testing') as ezid_account,

doc.doctype,

id.guid, 

'https://cn-stage.dataone.org/cn/v1/resolve/' || id.guid as url, 

text(null) as title, 

text(null) as creator

into table doi_registration 

from identifier id, xml_documents doc

where guid like 'doi%'

and id.docid = doc.docid;

-- update title using nodes

update doi_registration doi

set title = child.nodedata

from identifier id, xml_documents docs, xml_nodes nodes, xml_nodes parent, xml_nodes child

where doi.guid = id.guid

and 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 = 'title'

and parent.nodename = 'dataset';

-- update creator using nodes

-- creator/individualName/surName

-- creator/individualName/givenName

-- TODO: use only surname since it is required?

update doi_registration doi

set creator = siblingchild.nodedata ||' '|| child.nodedata

from identifier id, 

xml_documents docs, 

xml_nodes nodes, 

xml_nodes parent, 

xml_nodes child, 

xml_nodes grandparent, 

xml_nodes sibling, 

xml_nodes siblingchild

where doi.guid = id.guid

and 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 parent.parentnodeid = grandparent.nodeid

and nodes.nodename = 'surName'

and parent.nodename = 'individualName'

and grandparent.nodename = 'creator'

and parent.nodeid = sibling.parentnodeid

and sibling.nodeid = siblingchild.parentnodeid

and sibling.nodename = 'givenName';

-- update title using revisions

update doi_registration doi

set title = child.nodedata

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

where doi.guid = id.guid

and 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 = 'title'

and parent.nodename = 'dataset';

-- update creator using revisions

update doi_registration doi

set creator = siblingchild.nodedata ||' '|| child.nodedata

from identifier id, 

xml_revisions docs, 

xml_nodes_revisions nodes, 

xml_nodes_revisions parent, 

xml_nodes_revisions child, 

xml_nodes_revisions grandparent, 

xml_nodes_revisions sibling, 

xml_nodes_revisions siblingchild

where doi.guid = id.guid

and 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 parent.parentnodeid = grandparent.nodeid

and nodes.nodename = 'surName'

and parent.nodename = 'individualName'

and grandparent.nodename = 'creator'

and parent.nodeid = sibling.parentnodeid

and sibling.nodeid = siblingchild.parentnodeid

and sibling.nodename = 'givenName';

-- clean up

update doi_registration

set title = replace(title, E'\n', ' ');

COPY (select ezid_account, guid as doi, url, title, creator from doi_registration order by doi) 

TO '/tmp/doi_registration.csv'

WITH CSV HEADER;

--drop table doi_registration;