Project

General

Profile

« Previous | Next » 

Revision 7416

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.

View differences:

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