Revision 7416
Added by ben leinfelder about 12 years ago
| src/doi_registration.sql | ||
|---|---|---|
| 780 | 780 |
update doi_registration doi |
| 781 | 781 |
set pub_date = to_char(date_created, 'YYYY') |
| 782 | 782 |
from xml_documents x, identifier id |
| 783 |
where length(pub_date) > 4
|
|
| 783 |
where length(pub_date) != 4
|
|
| 784 | 784 |
and doi.guid = id.guid |
| 785 | 785 |
and id.docid = x.docid |
| 786 | 786 |
and id.rev = x.rev; |
| ... | ... | |
| 788 | 788 |
update doi_registration doi |
| 789 | 789 |
set pub_date = to_char(date_created, 'YYYY') |
| 790 | 790 |
from xml_revisions x, identifier id |
| 791 |
where length(pub_date) > 4
|
|
| 791 |
where length(pub_date) != 4
|
|
| 792 | 792 |
and doi.guid = id.guid |
| 793 | 793 |
and id.docid = x.docid |
| 794 | 794 |
and id.rev = x.rev; |
| ... | ... | |
| 810 | 810 |
set publisher = 'Baltimore Ecosystem Study LTER' |
| 811 | 811 |
where guid like '%knb-lter-bes%'; |
| 812 | 812 |
|
| 813 |
-- need URL identifiers for DataCite when they are not DOIs |
|
| 814 |
update doi_registration |
|
| 815 |
set obsoletedBy = 'https://cn.dataone.org/cn/v1/resolve/' || obsoletedBy |
|
| 816 |
where obsoletedBy not like 'doi%'; |
|
| 817 |
|
|
| 818 |
update doi_registration |
|
| 819 |
set obsoletes = 'https://cn.dataone.org/cn/v1/resolve/' || obsoletes |
|
| 820 |
where obsoletes not like 'doi%'; |
|
| 821 |
|
|
| 822 |
ALTER TABLE doi_registration |
|
| 823 |
ADD COLUMN obsoletedByIdType text, |
|
| 824 |
ADD COLUMN obsoletesIdType text, |
|
| 825 |
ADD COLUMN resourceMapLocationIdType text; |
|
| 826 |
|
|
| 827 |
update doi_registration |
|
| 828 |
set obsoletedByIdType = 'DOI' |
|
| 829 |
where obsoletedBy is not null |
|
| 830 |
and obsoletedBy like 'doi%'; |
|
| 831 |
|
|
| 832 |
update doi_registration |
|
| 833 |
set obsoletedByIdType = 'URL' |
|
| 834 |
where obsoletedBy is not null |
|
| 835 |
and obsoletedBy not like 'doi%'; |
|
| 836 |
|
|
| 837 |
update doi_registration |
|
| 838 |
set obsoletesIdType = 'DOI' |
|
| 839 |
where obsoletes is not null |
|
| 840 |
and obsoletes like 'doi%'; |
|
| 841 |
|
|
| 842 |
update doi_registration |
|
| 843 |
set obsoletesIdType = 'URL' |
|
| 844 |
where obsoletes is not null |
|
| 845 |
and obsoletes not like 'doi%'; |
|
| 846 |
|
|
| 847 |
update doi_registration |
|
| 848 |
set resourceMapLocationIdType = 'URL' |
|
| 849 |
where resourceMapLocation is not null; |
|
| 850 |
|
|
| 813 | 851 |
-- copy to the external file |
| 814 | 852 |
COPY |
| 815 | 853 |
(select ezid_account, |
| ... | ... | |
| 823 | 861 |
split_part(resourceType, '/', 2) as datacite_resourceType, |
| 824 | 862 |
objectFormat as datacite_format, |
| 825 | 863 |
obsoletedBy as datacite_relatedIdentifier_isPreviousVersionOf, |
| 864 |
obsoletedByIdType as datacite_relatedIdentifier_isPreviousVersionOfType, |
|
| 826 | 865 |
obsoletes as datacite_relatedIdentifier_isNewVersionOf, |
| 866 |
obsoletesIdType as datacite_relatedIdentifier_isNewVersionOfType, |
|
| 827 | 867 |
resourceMapLocation as datacite_relatedIdentifier_isPartOf, |
| 868 |
resourceMapLocationIdType as datacite_relatedIdentifier_isPartOfType, |
|
| 828 | 869 |
access as d1_read_access |
| 829 | 870 |
from doi_registration |
| 830 | 871 |
where access = 'public' |
Also available in: Unified diff
update pub_date when the length of that field is != 4 (use date_created in this scenario). There were 2 entries that had "193" as the pub_date.