Local Terms need to be updated in many sites

[laceysanderson]

Task Description

In PR #1727 we moved a number of terms into a single local cv as described here:

At some point in the history of Tripal, we created a number of chado table focused cvs (e.g. feature_property). All terms in these cvs had dbxref's in the 'local' db. This bloats the cv table a bit and gives more credibility to these terms then they deserve as they were created for convenience and to fill gaps in ontologies.

This PR moves all those terms into the local cv and db since that is exactly what they are.

Unfortunatly that PR did not include an update hook so anyone who had installed Tripal before that point (i.e. earlier in T4 or even in another version of Tripal) still has all the old vocabularies. This actually causes issues with TripalTerms which expect the local vocabulary. 🙈 Specifically, this causes the prepare step to fail on previous Tripal chado instances and also may cause Tripal to be unable to find these terms.

This core task is to add an update hook and/or a drush script to help people migrate these terms to the correct cv.

Branch Name

tv4g2-issue1911-moveLocalTerms

[dsenalik]

We might want to consider also moving the four terms in the discontinued CO_010 ontology to local, since so far I cannot find any other ontology that is suitable, and we could include in the update hook. Issue #1902 Opinions requested 😕

[dsenalik]

Here is some SQL that should perform this task, if you want to get your Tripal 3 instance ready ahead of time. First, to see what will be changed:

SELECT * FROM chado.cvterm WHERE cv_id IN (SELECT cv_id FROM chado.cv WHERE name IN
      ('organism_property', 'analysis_property', 'tripal_phylogeny',
       'featuremap_units', 'featurepos_property', 'featuremap_property',
       'study_property', 'nd_experiment_types', 'nd_geolocation_property',
       'tripal_analysis', 'library_property', 'library_type',
       'project_property');
 cvterm_id | cv_id |              name               |                                      definition...
-----------+-------+---------------------------------+------------------------------------------------...
      2889 |    33 | Project Type                    | A type of project
      2890 |    43 | Genotyping                      | An experiment where genotypes of individuals ar...
... and so on, remainder not shown
(47 rows)

Now you can make the changes. Test on a test site or backup first 😱

UPDATE chado.cvterm
  SET cv_id=(SELECT cv_id FROM chado.cv WHERE name='local')
  WHERE cv_id IN
    (SELECT cv_id FROM chado.cv WHERE name IN
      ('organism_property', 'analysis_property', 'tripal_phylogeny',
       'featuremap_units', 'featurepos_property', 'featuremap_property',
       'study_property', 'nd_experiment_types', 'nd_geolocation_property',
       'tripal_analysis', 'library_property', 'library_type',
       'project_property')
    );
UPDATE 47

DELETE FROM chado.cv
  WHERE cv_id IN
    (SELECT cv_id FROM chado.cv WHERE name IN
      ('organism_property', 'analysis_property', 'tripal_phylogeny',
       'featuremap_units', 'featurepos_property', 'featuremap_property',
       'study_property', 'nd_experiment_types', 'nd_geolocation_property',
       'tripal_analysis', 'library_property', 'library_type',
       'project_property')
    );
DELETE 13