Delete inactive grant relationships and associated metadata

[paulalbert1]

Problem

RAC has an unfortunate habit of removing grants from the InfoEd view. The existing code removes the grants (mostly) from VIVO but not the role data. This leads to seeing the missing activity data error like so...

Analysis

When we run this query against InfoEd, we get 7,965 roles on grants.

select CWID, Account_Number from WC_InfoEdProd_Integration.dbo.VIVO
where Confidential = 'N'

We can identify all grant roles in VIVO using this query.

PREFIX core: <http://vivoweb.org/ontology/core#> 
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX wcmc: <http://weill.cornell.edu/vivo/ontology/wcmc#>
PREFIX vitro: <http://vitro.mannlib.cornell.edu/ns/vitro/0.7#>
PREFIX obo: <http://purl.obolibrary.org/obo/> 
select ?s ?o
{
      GRAPH <http://vitro.mannlib.cornell.edu/a/graph/wcmcCoeus> { ?s <http://purl.obolibrary.org/obo/RO_0000053> ?o . }
}

There are 357 roles in the second query but not the first. All these roles, and their associated metadata need to be deleted from VIVO: GrantRolesToDelete-2021-12-07.csv

Here's an example query that lists the associated metadata for expired grant role data in VIVO.

PREFIX core: <http://vivoweb.org/ontology/core#> 
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX wcmc: <http://weill.cornell.edu/vivo/ontology/wcmc#>
PREFIX vitro: <http://vitro.mannlib.cornell.edu/ns/vitro/0.7#>
PREFIX obo: <http://purl.obolibrary.org/obo/> 
select ?p ?o
{
      GRAPH <http://vitro.mannlib.cornell.edu/a/graph/wcmcCoeus> { <https://vivo.med.cornell.edu/individual/role-kp-0000040134-yui4001> ?p ?o . }
}

Requirement

Kindly delete role data when grants are in VIVO but no longer in the InfoEd view. This should remove the dreaded "missing activity" error.

More info

Here is the format of some queries I used to delete incorrect triples:

curl -i -d '[email protected]' -d 'password=XXXXXXX' -d 'update=PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX core: <http://vivoweb.org/ontology/core#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX wcmc: <http://weill.cornell.edu/vivo/ontology/wcmc#> PREFIX vitro: <http://vitro.mannlib.cornell.edu/ns/vitro/0.7#> PREFIX obo: <http://purl.obolibrary.org/obo/> DELETE DATA { GRAPH <http://vitro.mannlib.cornell.edu/a/graph/wcmcCoeus> { <https://vivo.med.cornell.edu/individual/cwid-hgp2001> <http://purl.obolibrary.org/obo/RO_0000053>  <https://vivo.med.cornell.edu/individual/role-pisa-0000050069-hgp2001> . }}' 'http://XXXXXXX/api/sparqlUpdate'


curl -i -d '[email protected]' -d 'password=XXXXXXX' -d 'update=PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX core: <http://vivoweb.org/ontology/core#> PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#> PREFIX wcmc: <http://weill.cornell.edu/vivo/ontology/wcmc#> PREFIX vitro: <http://vitro.mannlib.cornell.edu/ns/vitro/0.7#> PREFIX obo: <http://purl.obolibrary.org/obo/> DELETE DATA { GRAPH <http://vitro.mannlib.cornell.edu/a/graph/wcmcCoeus>  {  <https://vivo.med.cornell.edu/individual/cwid-zhz9010> <http://vivoweb.org/ontology/core#relatedBy> <https://vivo.med.cornell.edu/individual/grant-0000039922> . }}' 'http://XXXXXXX/api/sparqlUpdate'