Failed to add new traits in rawphenotypes manged project.

[reynoldtan]

PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "dbxref_pkey" DETAIL: Key (dbxref_id)=(3386965) already exists.: INSERT INTO chado.dbxref (db_id, accession, version, description) VALUES (:db_id, :accession, :version, :description); Array ( [:db_id] => 662 [:accession] => Days till 50% of plant have one open flower (R1; days) [:version] => [:description] => ) in chado_query() (line 1538 of /var/www/portal/sites/all/modules/contrib/tripal/tripal_core/api/tripal_core.chado_query.api.inc).

Error above show in the error log when adding a new column header in the project management page. On initial review of code, it shows that when inserting a cvterm a counter points to an existing dbxref_id hence the duplicate key error. Examining terms in the portal show that they have been inserted in db - tripal instead of db - rawpheno_tripal. I suspect that the traits were inserted in the first release and recent updates did not include a function to map them to the correct db. Furthermore, @laceysanderson thinks that this db discrepancy is causing sequence counter/serial number in tripal_insert_cvterm() to return incorrect count.

This is a suggested fix. Although fresh reflects portal, adding new headers to it causes no error.

I have tested the upadate by

1. Uninstalling the module.
2. Deleted rawpheno_tripal in chado.db

3. Replaced rawpheno_tripal to tripal in all instance of tripal_insert_cvterm() in .install file.

   $cvterm = tripal_insert_cvterm( array( 'id' => 'tripal:' . $header, // 'replace back to rawpheno_tripal:' 'name' => $header, 'definition' => $cvterm_definition, 'cv_name' => $cvterm_type ) );

This will replicate the db source of terms in the portal.

4. re-Installed the module. All module cvtem should be linked to tripal db.
   To check, view a vocabulary term and see the database details below.
5. Run update. Should map all module cvterms to rawpheno_tripal. To check, view a vocabulary term and see the database details below.
6. Added a new term.
7. Revert the changes (step 3) in .install file.

Thanks!

[laceysanderson]

Tested on dev/fresh by checking the database before and after using the following query.

SELECT dbx.db_id, count(*) 
FROM chado.cvterm cvt 
LEFT JOIN chado.dbxref dbx ON cvt.dbxref_id=dbx.dbxref_id 
WHERE cv_id IN (SELECT cv_id FROM chado.cv WHERE name~'phenotype') 
GROUP BY dbx.db_id;

Which goes from

 db_id | count 
-------+-------
   547 |    39
   662 |   117

to

 db_id | count 
-------+-------
   662 |   156

where

 db_id |      name       
-------+-----------------
   547 | tripal
   662 | rawpheno_tripal

[laceysanderson]

Note: This was to fix Issue #17 (should have been mentioned in the initial pull request description).