Improving the robustness of Organism-DOI mapping

[sriramkanakam87]

In order to trace the source of information it was decided to make the organism-DOI mapping more robust. Till now we only used a '|' to separate organisms and DOIs in their respective columns. But this doesn't ensure we can map which organism belongs to which DOI. Since organisms and DOIs have many-to-many relation, it was decided to separate each organism by '|' and then multiple DOIs of each organism are to be separated by ';' which the DOIs of one organism and another are separated by '|'. As far as the parts are concerned, parts are related to any DOI through the organism, hence they would follow the same format as the DOIs in their mapping to organism.

To implement this, the following steps are to be done:

• Step1: Identify the entries where the mapping has to be improved.
• Step2: Import all the organisms where mapping doesn't affect. These are the ones where there is only one organism and one doi.
• Step3: Deliver the entries that need improved mapping to Kohulan (download).
• Step4: Once the remapped entries are provided, the entires table needs to be updated with this enhanced mapping.
• Step5: Then prepare a command to import these organisms into the organism table. Then, make the Citation mapping through M-M relation in the pivot.
• Step6: Ensure the details are correctly reflected in the Compound-details page.

[sriramkanakam87]

Step3

Entry links verification (inflamnat included)

During step3 it was noticed that there needs to be a re-verification of the links for all the entries from Inflamnat dataset. As a cautionary step, it was decided to check links for other datasets. As and when these files are provided, they have to be uploaded to the FSU cloud with the same name followed by a version number and then documented here as well with their links in the FSU cloud.

All other collection source links seem to be fine upon checking the sample rows from entires table.

The updated csv with the links for inflamnat is here.

PR for this is here

[sriramkanakam87]

Organism-parts

Organism parts are only provided by Phyto4health and NPASS. The mapping info so far provided by Kohulan on these collections is here.

[sriramkanakam87]

Step3

Entries for which source links may be found

On further digging, the entries where links are completely missing are collected and their collection names are extracted. But in the collection_molecule table, some entries from these supposedly link-less collections do seem to have links.
with t1 as (SELECT DISTINCT collection_id as id from entries where link is null or link='' or link=' '), t2 as (SELECT * from collection_molecule where (url like '%http%') and collection_id in (SELECT id from t1)) SELECT t2.id,t2.collection_id,t2.molecule_id,t2.url,t2.reference, c.title from t2 join collections c on t2.collection_id=c.id;

This means another scrapping needs to be done for the entries from these collections (where links are missing) to see if we can get their links.
with t1 as (SELECT id, reference_id, doi, link, organism,organism_part,collection_id from entries e where (link is null or link='' or link=' ') and collection_id in (30,19,25,54,58,42,63)) SELECT t1.*,c.title from t1 join collections c on t1.collection_id=c.id;