Revision 7320
Added by ben leinfelder almost 12 years ago
| doi_registration.sql | ||
|---|---|---|
| 454 | 454 |
|
| 455 | 455 |
--update creator usinig LDAP lookup |
| 456 | 456 |
CREATE TABLE ecoinfo_dn (dn text, givenName text, sn text); |
| 457 |
--TRUNCATE TABLE ecoinfo_dn; |
|
| 457 | 458 |
COPY ecoinfo_dn FROM '/tmp/ecoinfo_dn.csv' WITH CSV HEADER; |
| 458 | 459 |
|
| 459 | 460 |
update doi_registration |
| ... | ... | |
| 464 | 465 |
givenName|| ' ' || sn |
| 465 | 466 |
end |
| 466 | 467 |
from ecoinfo_dn |
| 467 |
where creator = dn;
|
|
| 468 |
where trim(both from lower(creator)) = lower(dn);
|
|
| 468 | 469 |
|
| 469 | 470 |
update doi_registration |
| 470 | 471 |
set publisher = |
| ... | ... | |
| 474 | 475 |
givenName|| ' ' || sn |
| 475 | 476 |
end |
| 476 | 477 |
from ecoinfo_dn |
| 477 |
where publisher = dn;
|
|
| 478 |
where trim(both from lower(publisher)) = lower(dn);
|
|
| 478 | 479 |
|
| 479 | 480 |
select creator, count(*) as cnt |
| 480 | 481 |
from doi_registration |
| ... | ... | |
| 482 | 483 |
group by creator |
| 483 | 484 |
order by cnt desc; |
| 484 | 485 |
|
| 486 |
-- update the data files with owner/publisher from EML entry |
|
| 487 |
update doi_registration doi |
|
| 488 |
set creator = meta.creator, |
|
| 489 |
publisher = meta.publisher |
|
| 490 |
from doi_registration meta, identifier id, ecogrid_docids eco |
|
| 491 |
where doi.guid = id.guid |
|
| 492 |
and id.docid||'.'||id.rev = eco.data_docid |
|
| 493 |
and eco.guid = meta.guid; |
|
| 494 |
|
|
| 485 | 495 |
-- copy to the external file |
| 486 | 496 |
COPY |
| 487 | 497 |
(select ezid_account, |
Also available in: Unified diff
updated LDAP dump and corrected missing entries that had been removed from LDAP.