1
|
select
|
2
|
text('testing') as ezid_account,
|
3
|
doc.doctype,
|
4
|
id.guid,
|
5
|
'https://cn-stage.dataone.org/cn/v1/resolve/' || id.guid as url,
|
6
|
text(null) as title,
|
7
|
text(null) as creator
|
8
|
into table doi_registration
|
9
|
from identifier id, xml_documents doc
|
10
|
where guid like 'doi%'
|
11
|
and id.docid = doc.docid;
|
12
|
|
13
|
-- update title using nodes
|
14
|
update doi_registration doi
|
15
|
set title = child.nodedata
|
16
|
from identifier id, xml_documents docs, xml_nodes nodes, xml_nodes parent, xml_nodes child
|
17
|
where doi.guid = id.guid
|
18
|
and id.docid = docs.docid
|
19
|
and id.rev = docs.rev
|
20
|
and docs.rootnodeid = nodes.rootnodeid
|
21
|
and nodes.nodeid = child.parentnodeid
|
22
|
and nodes.parentnodeid = parent.nodeid
|
23
|
and nodes.nodename = 'title'
|
24
|
and parent.nodename = 'dataset';
|
25
|
|
26
|
-- update creator using nodes
|
27
|
-- creator/individualName/surName
|
28
|
-- creator/individualName/givenName
|
29
|
-- TODO: use only surname since it is required?
|
30
|
update doi_registration doi
|
31
|
set creator = siblingchild.nodedata ||' '|| child.nodedata
|
32
|
from identifier id,
|
33
|
xml_documents docs,
|
34
|
xml_nodes nodes,
|
35
|
xml_nodes parent,
|
36
|
xml_nodes child,
|
37
|
xml_nodes grandparent,
|
38
|
xml_nodes sibling,
|
39
|
xml_nodes siblingchild
|
40
|
where doi.guid = id.guid
|
41
|
and id.docid = docs.docid
|
42
|
and id.rev = docs.rev
|
43
|
and docs.rootnodeid = nodes.rootnodeid
|
44
|
and nodes.nodeid = child.parentnodeid
|
45
|
and nodes.parentnodeid = parent.nodeid
|
46
|
and parent.parentnodeid = grandparent.nodeid
|
47
|
and nodes.nodename = 'surName'
|
48
|
and parent.nodename = 'individualName'
|
49
|
and grandparent.nodename = 'creator'
|
50
|
and parent.nodeid = sibling.parentnodeid
|
51
|
and sibling.nodeid = siblingchild.parentnodeid
|
52
|
and sibling.nodename = 'givenName';
|
53
|
|
54
|
|
55
|
-- update title using revisions
|
56
|
update doi_registration doi
|
57
|
set title = child.nodedata
|
58
|
from identifier id, xml_revisions docs, xml_nodes_revisions nodes, xml_nodes_revisions parent, xml_nodes_revisions child
|
59
|
where doi.guid = id.guid
|
60
|
and id.docid = docs.docid
|
61
|
and id.rev = docs.rev
|
62
|
and docs.rootnodeid = nodes.rootnodeid
|
63
|
and nodes.nodeid = child.parentnodeid
|
64
|
and nodes.parentnodeid = parent.nodeid
|
65
|
and nodes.nodename = 'title'
|
66
|
and parent.nodename = 'dataset';
|
67
|
|
68
|
|
69
|
-- update creator using revisions
|
70
|
update doi_registration doi
|
71
|
set creator = siblingchild.nodedata ||' '|| child.nodedata
|
72
|
from identifier id,
|
73
|
xml_revisions docs,
|
74
|
xml_nodes_revisions nodes,
|
75
|
xml_nodes_revisions parent,
|
76
|
xml_nodes_revisions child,
|
77
|
xml_nodes_revisions grandparent,
|
78
|
xml_nodes_revisions sibling,
|
79
|
xml_nodes_revisions siblingchild
|
80
|
where doi.guid = id.guid
|
81
|
and id.docid = docs.docid
|
82
|
and id.rev = docs.rev
|
83
|
and docs.rootnodeid = nodes.rootnodeid
|
84
|
and nodes.nodeid = child.parentnodeid
|
85
|
and nodes.parentnodeid = parent.nodeid
|
86
|
and parent.parentnodeid = grandparent.nodeid
|
87
|
and nodes.nodename = 'surName'
|
88
|
and parent.nodename = 'individualName'
|
89
|
and grandparent.nodename = 'creator'
|
90
|
and parent.nodeid = sibling.parentnodeid
|
91
|
and sibling.nodeid = siblingchild.parentnodeid
|
92
|
and sibling.nodename = 'givenName';
|
93
|
|
94
|
-- clean up
|
95
|
update doi_registration
|
96
|
set title = replace(title, E'\n', ' ');
|
97
|
|
98
|
COPY (select ezid_account, guid as doi, url, title, creator from doi_registration order by doi)
|
99
|
TO '/tmp/doi_registration.csv'
|
100
|
WITH CSV HEADER;
|
101
|
|
102
|
--drop table doi_registration;
|