You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
If we start using ReCiter instead of PubAdmin, we can't kill PubAdmin because VIVO Dashboard gets files from PubAdmin. Here are the existing queries.
In order for this to proceed, this issue needs to be addressed.
The below queries run against ReCiterDb should be able to replace the existing queries run against PubAdmin.
Articles
select distinct
scopusDocID as publication,
max(articleTitle) as label,
max(date_format(publicationDateStandardized, '%Y-%m-%d %T')) as date,
max(case
when issn <> '' then concat("http://vivo.med.cornell.edu/journal/",replace(issn,'-',''))
when journalTitleVerbose <> '' then concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleVerbose))
else concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleISOabbreviation))
end) as journal,
max(volume) as volume,
max(issue) as issue,
max(case
when doi is not null and doi != '' then concat("DOI: ",doi)
else ""
end) as doi,
max(pmid) as pmid,
max(pmcid) as pmcid,
scopusDocID as scopusID,
max(pages) AS pageStart,
NULL AS pageEnd,
max(timesCited) as timesCited,
max(publicationTypeCanonical) as pubtype
from personArticle d
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and articleTitle is not null
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
group by scopusDocID
Journals
select distinct
replace(concat("http://vivo.med.cornell.edu/journal/",issn),'-','') as URI,
max(journalTitleVerbose) as label,
issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and articleTitle is not null
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and userAssertion = 'ACCEPTED'
and issn <> ''
and scopusDocID != '0'
group by issn
UNION
(select distinct
concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleVerbose)) as URI,
journalTitleVerbose as label,
'' as issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and articleTitle is not null
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and issn = ''
and journalTitleVerbose <> ''
group by journalTitleVerbose)
UNION
(select distinct
concat("http://vivo.med.cornell.edu/journal/",md5(journalTitleISOabbreviation)) as URI,
max(journalTitleVerbose) as label,
issn,
'' as eissn,
'' as isbn13,
'' as isbn10,
'http://purl.org/ontology/bibo/Journal' as type
from personArticle
where not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and articleTitle is not null
and journalTitleVerbose is not null
and scopusDocID is not null
and scopusDocID != ''
and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and issn = ''
and journalTitleISOabbreviation <> ''
group by journalTitleISOabbreviation)
Authors
select distinct
min(authorshipURI) as authorshipURI,
max(label) as label,
max(firstName) as firstName,
max(lastName) as lastName,
max(cwid) as cwid,
max(affiliation) as affiliation,
max(department) as department,
min(RDFtypes) as RDFtypes,
max(popsURI) as popsURI,
max(directoryURI) as directoryURI,
max(vivoURI) as vivoURI
from
((select distinct
rank, p.pmid,
case
when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/cwid-', cast(e.cwid as char(20)))
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/person', cast(a.id as char(20)))
end as authorshipURI,
case
when e.cwid is not null then concat(e.surname,', ',e.givenName, if(middleName is not null, concat(' ',middleName),''))
when e.cwid is null then replace(concat(authorLastName,', ',authorFirstName),'.','')
end as label,
case
when e.cwid is not null then concat(e.givenName,if(middleName is not null, concat(' ',middleName),''))
when e.cwid is null then replace(givenName,'.','')
end as firstName,
case
when e.cwid is not null then e.surname
when e.cwid is null then replace(surname,'.','')
end as lastName,
e.cwid as cwid,
e.primaryOrg as affiliation,
e.primaryAcademicDepartment as department,
concat(
if(fullTimeFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#FullTimeWCMCFaculty|',''),
if(postdoc = 'yes','http://vivoweb.org/ontology/core#Postdoc|',''),
if(studentMD = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentMDNYC|',''),
if(studentMDPhD = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentMdPhdTriI|',''),
if(studentPhDTriI = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentPhdTriI|',''),
if(studentPhDWeill = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#StudentPhdWeill|',''),
if(partTimeFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#PartTimeWCMCFaculty|',''),
if(voluntaryFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#VoluntaryFaculty|',''),
if(emeritusFaculty = 'yes','http://vivoweb.org/ontology/core#EmeritusFaculty|',''),
if(adjunctFaculty = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#AdjunctFaculty|',''),
if(fellow = 'yes','http://weill.cornell.edu/vivo/ontology/wcmc#Fellow|',''),
if(faculty = 'yes','http://vivoweb.org/ontology/core#FacultyMember|',''),
if(nonFaculty = 'yes','http://vivoweb.org/ontology/core#NonAcademic|',''),
'http://xmlns.com/foaf/0.1/Person'
) as RDFtypes,
e.popsProfile as popsURI,
e.directoryProfile as directoryURI,
e.vivoProfile as vivoURI
from personArticleAuthor a
left join identity e on e.cwid = a.personIdentifier
left join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
where targetAuthor = '1'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and scopusDocID <> ''
and scopusDocID != '0'
and e.cwid is not null
UNION
select distinct
rank, p.pmid,
concat('http://vivo.med.cornell.edu/individual/person', cast(a.id as char(20))) as authorshipURI,
replace(concat(authorLastName,', ',authorFirstName),'.','') as label,
replace(authorFirstName,'.','') as firstName,
replace(authorLastName,'.','') as lastName,
null as cwid,
null as affiliation,
null as department,
'http://xmlns.com/foaf/0.1/Person' as RDFtypes,
null as popsURI,
null as directoryURI,
null as vivoURI
from personArticleAuthor a
join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
where targetAuthor = '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and scopusDocID <> ''
and scopusDocID != '0' )) x
group by pmid, rank
Authorships
select authorship, label, rank, publication, min(person) as person, max(vcard) as vcard from
(select distinct
a.id as id,
concat(
'http://vivo.med.cornell.edu/individual/pubid',
cast(scopusDocID as char(30)),'authorship',
cast(rank as char(20))
) as authorship,
NULL as label,
rank,
scopusDocID as publication,
case
when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/cwid-',cast(e.cwid as char(20)))
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/person',cast(a.id as char(20)))
end as person,
case
when e.cwid is not null then concat('http://vivo.med.cornell.edu/individual/arg2000028-cwid-',cast(e.cwid as char(20)))
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/arg2000028-person',cast(a.id as char(20)))
end as vcard
FROM personArticleAuthor a
inner join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
left join identity e on e.cwid = a.personIdentifier
where
scopusDocID is not null and scopusDocID != '' and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and targetAuthor = '1'
UNION
select distinct a.id as id, concat(
'http://vivo.med.cornell.edu/individual/pubid',
cast(scopusDocID as char(30)),'authorship',
cast(rank as char(20))
) as authorship,
NULL as label,
rank,
scopusDocID as publication,
concat('http://vivo.med.cornell.edu/individual/person',cast(a.id as char(20))) as person,
case
when e.cwid is null then concat('http://vivo.med.cornell.edu/individual/arg2000028-person',cast(a.id as char(20)))
end as vcard
FROM personArticleAuthor a
inner join personArticle p on p.pmid = a.pmid and p.personIdentifier = a.personIdentifier
left join identity e on e.cwid = a.personIdentifier
where
scopusDocID is not null and scopusDocID != '' and scopusDocID != '0'
and userAssertion = 'ACCEPTED'
and not (articleTitle like ("%: Retraction") or articleTitle like ("%[Retraction]%") or articleTitle like ("Retraction:%") or articleTitle like ("Retraction.%") or articleTitle like ("[Erratum%") or articleTitle like("Erratum%") or articleTitle like ("ERRATUMERRATUM%") or articleTitle like ("Withdrawn%"))
and targetAuthor = '0'
group by rank, scopusDocID) x
group by authorship
The text was updated successfully, but these errors were encountered:
Context
If we start using ReCiter instead of PubAdmin, we can't kill PubAdmin because VIVO Dashboard gets files from PubAdmin. Here are the existing queries.
In order for this to proceed, this issue needs to be addressed.
The below queries run against ReCiterDb should be able to replace the existing queries run against PubAdmin.
Articles
Journals
Authors
Authorships
The text was updated successfully, but these errors were encountered: