Metacat

/**

 * Additional modifications, July 5th, 2012

 */

ALTER TABLE doi_registration

ADD COLUMN resourceType text,

ADD COLUMN objectFormat text,

ADD COLUMN obsoletedBy text,

ADD COLUMN obsoletes text,

ADD COLUMN resourceMapId text,

ADD COLUMN resourceMapLocation text,

ADD COLUMN access text;

-- update access values

update doi_registration

set access = 'protected';

update doi_registration doi

set access = 'public'

from xml_access a

where doi.guid = a.guid

and a.principal_name = 'public'

and a.permission >= '4'

and a.perm_type = 'allow';

-- update the objectFormat from xml_documents

update doi_registration doi

set objectFormat = doc.doctype

from identifier id, xml_documents doc

where doi.guid = id.guid

and id.docid = doc.docid;

-- update the objectFormat from xml_revisions

update doi_registration doi

set objectFormat = rev.doctype

from identifier id, xml_revisions rev

where doi.guid = id.guid

and id.docid = rev.docid;

--update resourceType

update doi_registration

set resourceType = 'Dataset/METADATA';

update doi_registration

set resourceType = 'Dataset/DATA'

where objectFormat = 'BIN';

-- update the objectFormat from SM table (will be subset)

update doi_registration doi

set objectFormat = sm.object_format

from systemMetadata sm

where doi.guid = sm.guid;

--16938

--update revision history from SM

update doi_registration doi

set obsoletes = sm.obsoletes,

obsoletedBy = sm.obsoleted_by

from systemMetadata sm

where doi.guid = sm.guid;

--update resourceMap pointer TBD

-- NOTE: some of these maps might not actually exist

update doi_registration doi

set resourceMapId = 'resourceMap_'||id.docid||'.'||id.rev

from identifier id

where doi.guid = id.guid

and id.docid = rev.docid

and objectFormat like 'eml%';

--update the resourcemapid for described data

CREATE TABLE ecogrid_docids AS

select distinct id.docid, id.rev, id.guid, substring(xpi.nodedata from 'ecogrid://knb/(.*)$') as data_docid

from xml_path_index xpi, identifier id, xml_documents xmld

where xpi.docid = xmld.docid 

and xmld.docid = id.docid

and xmld.rev = id.rev

and xpi.path like 'dataset/%/physical/distribution/online/url' 

and xpi.nodedata like 'ecogrid%';

-- TODO: include revisions for described data

-- NOTE: some of thee maps might not actually exist

update doi_registration doi

set resourceMapId = 'resourceMap_'||eco.docid||'.'||eco.rev

from identifier id, ecogrid_docids eco

where doi.guid = id.guid

and id.docid||'.'||id.rev = eco.data_docid;

--update the resource map location

update doi_registration

set resourceMapLocation = 'https://cn.dataone.org/cn/v1/resolve/' || resourceMapId

where resourceMapId is not null;

--update creator usinig LDAP lookup

-- copy to the external file

COPY 

(select ezid_account, 

guid as dc_identifier, 

url as datacite_url, 

title as dc_title, 

creator as dc_creator, 

publisher as dc_publisher, 

pub_date as datacite_publicationYear,

resourceType,

objectFormat,

obsoletedBy,

obsoletes,

resourceMapId,

resourceMapLocation,

access

from doi_registration 

order by doi) 

--drop table ecogrid_docids;