Revision 7324
Added by ben leinfelder over 12 years ago
| src/doi_registration.sql | ||
|---|---|---|
| 76 | 76 |
and docs.rootnodeid = nodes.rootnodeid |
| 77 | 77 |
and nodes.nodeid = child.parentnodeid |
| 78 | 78 |
and nodes.parentnodeid = parent.nodeid |
| 79 |
and nodes.nodename = 'publisher'
|
|
| 80 |
and parent.nodename = 'dataset'
|
|
| 81 |
and child.nodetype = 'TEXT';
|
|
| 79 |
and nodes.nodename = 'organizationName'
|
|
| 80 |
and parent.nodename = 'publisher'
|
|
| 81 |
and nodes.nodeid = (select min(thisnode.nodeid) from xml_nodes thisnode where thisnode.parentnodeid = parent.nodeid and nodetype = 'ELEMENT');
|
|
| 82 | 82 |
|
| 83 |
-- update publisher surName |
|
| 84 |
update doi_registration doi |
|
| 85 |
set publisher = child.nodedata |
|
| 86 |
from identifier id, |
|
| 87 |
xml_documents docs, |
|
| 88 |
xml_nodes nodes, |
|
| 89 |
xml_nodes parent, |
|
| 90 |
xml_nodes child, |
|
| 91 |
xml_nodes grandparent |
|
| 92 |
where doi.guid = id.guid |
|
| 93 |
and id.docid = docs.docid |
|
| 94 |
and id.rev = docs.rev |
|
| 95 |
and docs.rootnodeid = nodes.rootnodeid |
|
| 96 |
and nodes.nodeid = child.parentnodeid |
|
| 97 |
and nodes.parentnodeid = parent.nodeid |
|
| 98 |
and parent.parentnodeid = grandparent.nodeid |
|
| 99 |
and nodes.nodename = 'surName' |
|
| 100 |
and parent.nodename = 'individualName' |
|
| 101 |
and grandparent.nodename = 'publisher' |
|
| 102 |
and parent.nodeid = (select min(thisnode.nodeid) from xml_nodes thisnode where thisnode.parentnodeid = grandparent.nodeid and nodetype = 'ELEMENT'); |
|
| 103 |
|
|
| 104 |
-- add the first name if we have it |
|
| 105 |
update doi_registration doi |
|
| 106 |
set publisher = child.nodedata ||' '|| creator |
|
| 107 |
from identifier id, |
|
| 108 |
xml_documents docs, |
|
| 109 |
xml_nodes nodes, |
|
| 110 |
xml_nodes parent, |
|
| 111 |
xml_nodes child, |
|
| 112 |
xml_nodes grandparent |
|
| 113 |
where doi.guid = id.guid |
|
| 114 |
and id.docid = docs.docid |
|
| 115 |
and id.rev = docs.rev |
|
| 116 |
and docs.rootnodeid = nodes.rootnodeid |
|
| 117 |
and nodes.nodeid = child.parentnodeid |
|
| 118 |
and nodes.parentnodeid = parent.nodeid |
|
| 119 |
and parent.parentnodeid = grandparent.nodeid |
|
| 120 |
and nodes.nodename = 'givenName' |
|
| 121 |
and parent.nodename = 'individualName' |
|
| 122 |
and grandparent.nodename = 'publisher' |
|
| 123 |
and parent.nodeid = (select min(thisnode.nodeid) from xml_nodes thisnode where thisnode.parentnodeid = grandparent.nodeid and nodetype = 'ELEMENT'); |
|
| 124 |
|
|
| 83 | 125 |
-- update organization using nodes |
| 84 | 126 |
update doi_registration doi |
| 85 | 127 |
set creator = child.nodedata |
| ... | ... | |
| 174 | 216 |
and docs.rootnodeid = nodes.rootnodeid |
| 175 | 217 |
and nodes.nodeid = child.parentnodeid |
| 176 | 218 |
and nodes.parentnodeid = parent.nodeid |
| 177 |
and nodes.nodename = 'publisher'
|
|
| 178 |
and parent.nodename = 'dataset'
|
|
| 179 |
and child.nodetype = 'TEXT';
|
|
| 219 |
and nodes.nodename = 'organizationName'
|
|
| 220 |
and parent.nodename = 'publisher'
|
|
| 221 |
and nodes.nodeid = (select min(thisnode.nodeid) from xml_nodes_revisions thisnode where thisnode.parentnodeid = parent.nodeid and nodetype = 'ELEMENT');
|
|
| 180 | 222 |
|
| 223 |
-- use publisher individual name if we have it |
|
| 224 |
update doi_registration doi |
|
| 225 |
set publisher = child.nodedata |
|
| 226 |
from identifier id, |
|
| 227 |
xml_revisions docs, |
|
| 228 |
xml_nodes_revisions nodes, |
|
| 229 |
xml_nodes_revisions parent, |
|
| 230 |
xml_nodes_revisions child, |
|
| 231 |
xml_nodes_revisions grandparent |
|
| 232 |
where doi.guid = id.guid |
|
| 233 |
and id.docid = docs.docid |
|
| 234 |
and id.rev = docs.rev |
|
| 235 |
and docs.rootnodeid = nodes.rootnodeid |
|
| 236 |
and nodes.nodeid = child.parentnodeid |
|
| 237 |
and nodes.parentnodeid = parent.nodeid |
|
| 238 |
and parent.parentnodeid = grandparent.nodeid |
|
| 239 |
and nodes.nodename = 'surName' |
|
| 240 |
and parent.nodename = 'individualName' |
|
| 241 |
and grandparent.nodename = 'publisher' |
|
| 242 |
and parent.nodeid = (select min(thisnode.nodeid) from xml_nodes_revisions thisnode where thisnode.parentnodeid = grandparent.nodeid and nodetype = 'ELEMENT'); |
|
| 243 |
|
|
| 244 |
--include the first name (careful not to rerun this part) |
|
| 245 |
update doi_registration doi |
|
| 246 |
set publisher = child.nodedata ||' '|| creator |
|
| 247 |
from identifier id, |
|
| 248 |
xml_revisions docs, |
|
| 249 |
xml_nodes_revisions nodes, |
|
| 250 |
xml_nodes_revisions parent, |
|
| 251 |
xml_nodes_revisions child, |
|
| 252 |
xml_nodes_revisions grandparent |
|
| 253 |
where doi.guid = id.guid |
|
| 254 |
and id.docid = docs.docid |
|
| 255 |
and id.rev = docs.rev |
|
| 256 |
and docs.rootnodeid = nodes.rootnodeid |
|
| 257 |
and nodes.nodeid = child.parentnodeid |
|
| 258 |
and nodes.parentnodeid = parent.nodeid |
|
| 259 |
and parent.parentnodeid = grandparent.nodeid |
|
| 260 |
and nodes.nodename = 'givenName' |
|
| 261 |
and parent.nodename = 'individualName' |
|
| 262 |
and grandparent.nodename = 'publisher' |
|
| 263 |
and parent.nodeid = (select min(thisnode.nodeid) from xml_nodes_revisions thisnode where thisnode.parentnodeid = grandparent.nodeid and nodetype = 'ELEMENT'); |
|
| 264 |
|
|
| 181 | 265 |
-- update organization using nodes |
| 182 | 266 |
update doi_registration doi |
| 183 | 267 |
set creator = child.nodedata |
| ... | ... | |
| 432 | 516 |
where doi.guid = id.guid |
| 433 | 517 |
and id.docid = docs.docid |
| 434 | 518 |
and id.rev= docs.rev |
| 435 |
and creator is null;
|
|
| 519 |
and (creator is null or trim(creator) = '');
|
|
| 436 | 520 |
|
| 437 | 521 |
update doi_registration doi |
| 438 | 522 |
set publisher = user_owner |
| ... | ... | |
| 440 | 524 |
where doi.guid = id.guid |
| 441 | 525 |
and id.docid = docs.docid |
| 442 | 526 |
and id.rev= docs.rev |
| 443 |
and publisher is null;
|
|
| 527 |
and (publisher is null or trim(publisher) = '');
|
|
| 444 | 528 |
|
| 445 | 529 |
update doi_registration doi |
| 446 | 530 |
set creator = user_owner |
| ... | ... | |
| 448 | 532 |
where doi.guid = id.guid |
| 449 | 533 |
and id.docid = docs.docid |
| 450 | 534 |
and id.rev= docs.rev |
| 451 |
and creator is null;
|
|
| 535 |
and (creator is null or trim(creator) = '');
|
|
| 452 | 536 |
|
| 453 | 537 |
update doi_registration doi |
| 454 | 538 |
set publisher = user_owner |
| ... | ... | |
| 456 | 540 |
where doi.guid = id.guid |
| 457 | 541 |
and id.docid = docs.docid |
| 458 | 542 |
and id.rev= docs.rev |
| 459 |
and publisher is null;
|
|
| 543 |
and (publisher is null or trim(publisher) = '');
|
|
| 460 | 544 |
|
| 545 |
-- fix a previous mistake |
|
| 546 |
update doi_registration |
|
| 547 |
set publisher = null |
|
| 548 |
where publisher = 'document'; |
|
| 549 |
|
|
| 461 | 550 |
--update creator usinig LDAP lookup |
| 462 | 551 |
CREATE TABLE ecoinfo_dn (dn text, givenName text, sn text); |
| 463 | 552 |
--TRUNCATE TABLE ecoinfo_dn; |
| ... | ... | |
| 498 | 587 |
and id.docid||'.'||id.rev = eco.data_docid |
| 499 | 588 |
and eco.guid = meta.guid; |
| 500 | 589 |
|
| 590 |
-- clean up KBS entries that have empty user_owner |
|
| 591 |
update doi_registration |
|
| 592 |
set creator = 'Kellogg Biological Station' |
|
| 593 |
where (creator is null or trim(creator) = '') |
|
| 594 |
and guid like '%knb-lter-kbs%'; |
|
| 595 |
|
|
| 596 |
update doi_registration |
|
| 597 |
set publisher = 'Kellogg Biological Station' |
|
| 598 |
where (publisher is null or trim(publisher) = '') |
|
| 599 |
and guid like '%knb-lter-kbs%'; |
|
| 600 |
|
|
| 501 | 601 |
-- copy to the external file |
| 502 | 602 |
COPY |
| 503 | 603 |
(select ezid_account, |
Also available in: Unified diff
use correct children of 'publisher' element